package com.arpa.ntocc.basic.api.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.arpa.ntocc.basic.api.domain.entity.TableBackupConfig;
import com.arpa.ntocc.basic.api.domain.dto.TableBackupConfigDTO;
import com.arpa.ntocc.basic.api.domain.vo.TableBackupConfigVO;
import com.arpa.ntocc.basic.api.mapper.TableBackupConfigMapper;
import com.arpa.ntocc.basic.api.service.ITableBackupConfigService;
import com.arpa.ntocc.common.common.exception.ServiceException;
import com.arpa.ntocc.common.common.util.CommonUtil;
import com.arpa.ntocc.common.common.util.UserUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.base.Joiner;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.StringUtils;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.DateFormat;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 表数据备份配置 服务实现类
 * </p>
 *
 * @author LEO
 * @since 2021-06-08
 */
@Service
@Log4j2(topic = "business")
public class TableBackupConfigServiceImpl extends ServiceImpl<TableBackupConfigMapper, TableBackupConfig> implements ITableBackupConfigService {

    /**
     * 查询的数据条数
     */
    private static final Integer DATA_COUNT = 1000;

    /**
     * 保存
     *
     * @param tableBackupConfig
     * @return
     */
    @Override
    @Transactional(rollbackFor = {Exception.class})
    public boolean save(TableBackupConfig tableBackupConfig) {
        tableBackupConfig.setCode(null);
        this.checkTableName(tableBackupConfig);
        tableBackupConfig.setCode(IdUtil.simpleUUID());
        if (StringUtils.isBlank(tableBackupConfig.getIsUse())) {
            tableBackupConfig.setIsUse("N");
        }
        if (StringUtils.isBlank(tableBackupConfig.getCreatedBy())) {
            tableBackupConfig.setCreatedBy(UserUtil.getCode());
        }
        return super.save(tableBackupConfig);
    }

    /**
     * 根据code更新实体
     *
     * @param tableBackupConfig
     * @return
     */
    @Override
    @Transactional(rollbackFor = {Exception.class})
    public int updateByCode(TableBackupConfig tableBackupConfig) {
        this.checkTableName(tableBackupConfig);
        if (StringUtils.isBlank(tableBackupConfig.getIsUse())) {
            tableBackupConfig.setIsUse("N");
        }
        if (StringUtils.isBlank(tableBackupConfig.getModifiedBy())) {
            tableBackupConfig.setModifiedBy(UserUtil.getCode());
        }
        return baseMapper.update(tableBackupConfig, new QueryWrapper<TableBackupConfig>().lambda().eq(TableBackupConfig::getCode, tableBackupConfig.getCode()));
    }

    /**
     * 查询列表
     *
     * @param tableBackupConfigDTO
     * @return
     */
    @Override
    public List<TableBackupConfigVO> queryList(TableBackupConfigDTO tableBackupConfigDTO) {
        //排序字段名需要驼峰转数据库下划线类型字段名
        if (StringUtils.isNotEmpty(tableBackupConfigDTO.getSortField())) {
            tableBackupConfigDTO.setSortField(CommonUtil.camel2Underline(tableBackupConfigDTO.getSortField()));
        }
        return baseMapper.queryList(tableBackupConfigDTO);
    }

    /**
     * 查询合计,包含总数
     *
     * @param tableBackupConfigDTO
     * @return
     */
    @Override
    public TableBackupConfigVO queryListSum(TableBackupConfigDTO tableBackupConfigDTO) {
        return baseMapper.queryListSum(tableBackupConfigDTO);
    }

    @Override
    @Transactional
    @Async
    public void backupTableData() {
        // 1、查询配置的需要备份数据的表
        List<TableBackupConfig> tableBackupConfigList = baseMapper.selectList(new QueryWrapper<TableBackupConfig>().lambda().eq(TableBackupConfig::getIsUse, 'Y'));
        if (tableBackupConfigList.isEmpty()) {
            log.info("定时任务[备份表数据]，执行完成，任务结果：没有需要备份数据的表");
            return;
        }
        // 代码执行时的日期零点
        Date date = DateUtil.beginOfDay(new Date());
        // 主库表名
        String tableName;
        // 备份多长时间之前的数据
        int backupTime;
        // 备份此日期之前的数据
        String dateStr;
        // 获取要查询的列字段
        String tableFieldData;
        // 主库表要备份的数据
        List<Map<String, String>> tableData;
        // 获取插入表的列结构
        String columnData;
        // 插入从库的数据条数
        long insertCount;
        // 主库表要备份的数据条数
        long tableDataCount;
        // 主库删除的数据条数
        long deleteCount;
        // 指定起始时间
        String appointedDayStr = "";
        for (TableBackupConfig tableBackupConfig : tableBackupConfigList) {
            // 2、校验主库表是否存在，不存在则不处理
            tableName = baseMapper.queryTableIsExistInMaster(tableBackupConfig.getTableName());
            if (StrUtil.isBlank(tableName)) {
                log.error("定时任务[备份表数据]，主库表[{}]不存在", tableBackupConfig.getTableName());
                continue;
            }
            try {
                // 3、校验从库表是否存在，不存在则新增
                this.checkSlaveTable(tableBackupConfig);
                // 4、查询主库数据
                backupTime = tableBackupConfig.getBackupTime();
                dateStr = DateUtil.offsetDay(date, -backupTime).toString();
                // 备份天数 根据 指定起始时间 计算
                if (ObjectUtil.isNotNull(tableBackupConfig.getAppointedDay())){
                    // 备份 天数
                    dateStr = DateUtil.offsetDay(Date.from(tableBackupConfig.getAppointedDay().atZone(ZoneId.systemDefault()).toInstant()), -backupTime).toString();
                    // 起始 时间
                    appointedDayStr = DateFormat.getDateInstance().format(Date.from(tableBackupConfig.getAppointedDay().atZone(ZoneId.systemDefault()).toInstant()));
                }
                do {
                    // 指定时间 不为空
                    if (ObjectUtil.isNotNull(tableBackupConfig.getAppointedDay())){
                        // 指定时间 + 备份天数
                        tableDataCount = baseMapper.countAppointedDay(tableName, appointedDayStr, dateStr);
                    }else{
                        tableDataCount = baseMapper.countTableDataInMaster(tableName, dateStr);
                    }
                    if (tableDataCount == 0) {
                        log.info("定时任务[备份表数据]，备份主库表[{}]没有需要备份的数据", tableName);
                        break;
                    }
                    tableFieldData = this.getTableFieldData(tableName);
                    if (ObjectUtil.isNotNull(tableBackupConfig.getAppointedDay())){
                        tableData = baseMapper.queryAppointedDay(tableName, tableFieldData, appointedDayStr, dateStr,  DATA_COUNT);
                    }else{
                        tableData = baseMapper.queryTableDataInMaster(tableName, tableFieldData, dateStr, DATA_COUNT);
                    }
                    // 5、插入数据到从库
                    columnData = this.getColumnData(tableData.get(0));
                    insertCount = baseMapper.insertTableDataInSlave(tableBackupConfig.getBackupTableName(), columnData, tableData);
                    // 6、从库插入成功之后删除主库数据
                    if (insertCount == tableData.size()) {
                        if (ObjectUtil.isNotNull(tableBackupConfig.getAppointedDay())){
                            deleteCount = baseMapper.deleteAppointedDay(tableName, appointedDayStr, dateStr, DATA_COUNT);
                        }else{
                            deleteCount = baseMapper.deleteTableDataInMaster(tableName, dateStr, DATA_COUNT);
                        }

                        log.info("定时任务[备份表数据]，备份主库表[{}]完成，备份数据[{}]条，删除数据[{}]条", tableName, insertCount, deleteCount);
                    } else {
                        log.error("定时任务[备份表数据]，备份主库表[{}]未完成，应该备份数据[{}]条，实际备份数据[{}]条", tableName, tableData.size(), insertCount);
                    }
                } while (tableDataCount > DATA_COUNT);
            } catch (Exception e) {
                log.error("定时任务[备份表数据]，备份主库表[{}]异常，异常堆栈信息：", tableName, e);
            }
        }
    }

    /**
     * 获取要查询的列字段，即 SELECT xxx FROM tableName 语句中的xxx部分
     *
     * @param tableName 主表名
     * @return 处理之后的列字段
     */
    private String getTableFieldData(String tableName) {
        List<String> resultList = new ArrayList<>();
        List<Map<String, String>> tableColumnDataList = baseMapper.queryTableFieldDataInMaster(tableName);
        for (Map<String, String> map : tableColumnDataList) {
            String field = map.get("Field");
            String type = map.get("Type");
            // 不为null的不处理
            if ("NO".equalsIgnoreCase(map.get("Null"))) {
                resultList.add(field);
            }
            // 可以为null的处理
            else {
                // 例如：IFNULL(modified_by,'') AS modified_by
                // 暂时这样处理,针对不同数据Type指定不同缺省值
                if (type.contains("int") || type.contains("decimal")){
                    resultList.add("IFNULL(".concat(field).concat(",0) AS ").concat(field));
                } else{
                    resultList.add("IFNULL(".concat(field).concat(",'') AS ").concat(field));
                }
            }
        }
        return Joiner.on(",").join(resultList);
    }

    /**
     * 获取插入表的列结构，即 INSERT INTO tableName xxx VALUES() 语句中的xxx部分
     *
     * @param map 查询到的表数据的其中一条
     * @return 列结构
     */
    private String getColumnData(Map<String, String> map) {
        return "(".concat(Joiner.on(",").join(new ArrayList<>(map.keySet()))).concat(")");
    }

    /**
     * 校验从库表是否存在，不存在则新增
     */
    private void checkSlaveTable(TableBackupConfig tableBackupConfig) {
        String backupTableName = tableBackupConfig.getBackupTableName();
        String tableName = baseMapper.queryTableIsExistInSlave(backupTableName);
        if (StrUtil.isNotBlank(tableName)) {
            return;
        }
        log.info("定时任务[备份表数据]，从库表[{}]不存在", backupTableName);
        // 1、查询主库表数据结构
        Map<String, String> tableStructureMap = baseMapper.queryTableStructureInMaster(tableBackupConfig.getTableName());
        // 主库表数据结构
        String tableStructure = tableStructureMap.get("Create Table");
        // 从库表数据结构：替换主库表名为从库表名
        String backupTableStructure = tableStructure.replace(tableBackupConfig.getTableName(), backupTableName);
        // 2、在从库创建表
        baseMapper.createTableInSlave(backupTableStructure);
    }

    /**
     * 校验表名唯一性
     */
    private void checkTableName(TableBackupConfig tableBackupConfig) {
        if (StrUtil.isBlank(tableBackupConfig.getTableName())) {
            throw new ServiceException("主库表名不能为空");
        }
        tableBackupConfig.setTableName(tableBackupConfig.getTableName().trim());
        if (StrUtil.isBlank(tableBackupConfig.getTableName())) {
            throw new ServiceException("主库表名不能为空字符");
        }
        if (StrUtil.isBlank(tableBackupConfig.getBackupTableName())) {
            throw new ServiceException("备份到从库的表名不能为空");
        }
        tableBackupConfig.setBackupTableName(tableBackupConfig.getBackupTableName().trim());
        if (StrUtil.isBlank(tableBackupConfig.getBackupTableName())) {
            throw new ServiceException("备份到从库的表名不能为空字符");
        }
        // 校验主表是否存在
        String tableName = baseMapper.queryTableIsExistInMaster(tableBackupConfig.getTableName());
        if (StrUtil.isBlank(tableName)) {
            throw new ServiceException(StrUtil.format("主库表[{}]不存在", tableBackupConfig.getTableName()));
        }
        // 校验主库表名的唯一性
        LambdaQueryWrapper<TableBackupConfig> queryWrapper = new QueryWrapper<TableBackupConfig>().lambda().eq(TableBackupConfig::getTableName, tableBackupConfig.getTableName());
        if (StrUtil.isNotBlank(tableBackupConfig.getCode())) {
            queryWrapper.ne(TableBackupConfig::getCode, tableBackupConfig.getCode());
        }
        Integer count = baseMapper.selectCount(queryWrapper);
        if (count > 0) {
            throw new ServiceException("主库表名重复");
        }
        // 校验从库表名的唯一性
        queryWrapper = new QueryWrapper<TableBackupConfig>().lambda().eq(TableBackupConfig::getBackupTableName, tableBackupConfig.getBackupTableName());
        if (StrUtil.isNotBlank(tableBackupConfig.getCode())) {
            queryWrapper.ne(TableBackupConfig::getCode, tableBackupConfig.getCode());
        }
        count = baseMapper.selectCount(queryWrapper);
        if (count > 0) {
            throw new ServiceException("备份到从库的表名重复");
        }
    }
}
